#include "Controllers.h"


/***************URL PARSER**********************/

static char dec2hexChar(short int n) {
    if ( 0 <= n && n <= 9 ) {
        return char( short('0') + n );
    } else if ( 10 <= n && n <= 15 ) {
        return char( short('A') + n - 10 );
    } else {
        return char(0);
    }
}

static short int hexChar2dec(char c) {
    if ( '0'<=c && c<='9' ) {
        return short(c-'0');
    } else if ( 'a'<=c && c<='f' ) {
        return ( short(c-'a') + 10 );
    } else if ( 'A'<=c && c<='F' ) {
        return ( short(c-'A') + 10 );
    } else {
        return -1;
    }
}

static string encodeURL(const string &URL)
{
    string result = "";
    for ( unsigned int i=0; i<URL.size(); i++ ) {
        char c = URL[i];
        if (
                ( '0'<=c && c<='9' ) ||
                ( 'a'<=c && c<='z' ) ||
                ( 'A'<=c && c<='Z' ) ||
                c=='/' || c=='.'
                ) {
            result += c;
        } else {
            int j = (short int)c;
            if ( j < 0 ) {
                j += 256;
            }
            int i1, i0;
            i1 = j / 16;
            i0 = j - i1*16;
            result += '%';
            result += dec2hexChar(i1);
            result += dec2hexChar(i0);
        }
    }
    return result;
}

static string decodeURL(const string &URL) {
    string result = "";
    for ( unsigned int i=0; i<URL.size(); i++ ) {
        char c = URL[i];
        if (c == '+') {
            result += " ";
        }
        else if ( c != '%' ) {
            result += c;
        } else {
            char c1 = URL[++i];
            char c0 = URL[++i];
            int num = 0;
            num += hexChar2dec(c1) * 16 + hexChar2dec(c0);
            result += char(num);
        }
    }
    return result;
}

/*************************************/

string res = "";
string lastSql = "";

string testController(string params) {
	printf("[DEBUG] testController params: %s\n",params.c_str());
    return string("{\"msg\": \"hello,world\"}");
}
string testController2(string params) {
	printf("[DEBUG] testController2 params: %s\n",params.c_str());
	//updateTable("{\"tableName\":\"testTableName\",\"oldValue\":\"{\"id\":123,\"name\":\"fmhnb\"}\",\"newValue\":\"{\"id\":123,\"name\":\"fmhsb\"}\"}");
    return string("{\"msg2\": \"hello,world2\"}");
}

inline string replaceDquote(char * str)
{
    string::size_type startpos = 0;
    string newStr = string(str);
    while (startpos!= std::string::npos)
    {
        startpos = newStr.find('\"');   //找到'.'的位置
        if( startpos != std::string::npos ) //std::string::npos表示没有找到该字符
        {
            newStr = newStr.replace(startpos,1,"\'"); //实施替换，注意后面一定要用""引起来，表示字符串
        }
    }
    return newStr;
}

static int getTableContentCallback(void *data, int argc, char **argv, char **azColName) {
    int i;
    printf("%s: ", (const char *) data);
    res += "{";
    for (i = 0; i < argc; i++) {
        if (i + 1 == argc) {
            res += "\"" + string(azColName[i]) + "\":" + "\"" + string(argv[i] ? argv[i] : "NULL") + "\"";
        } else {
            res += "\"" + string(azColName[i]) + "\":" + "\"" + string(argv[i] ? argv[i] : "NULL") + "\",";
        }
    }
    res += "},";
    return 0;
}


string getColName(string tableName)
{
    sqlite3 *db;
    int rc = sqlite3_open("db.sqlite3", &db);
    string sql = "PRAGMA table_Info(\"" + tableName + "\")";
    cout << "[DEBUG]sql: " << sql << endl;

    char** pResult = nullptr;
    int nRow;
    int nCol;
    char *zErrMsg = nullptr;
    if (rc != SQLITE_OK)
    {
        printf("Can't open database: %s\n", sqlite3_errmsg(db));
        return "";
    }
    else {
        
        rc = sqlite3_get_table(db, sql.c_str(), &pResult, &nRow, &nCol, &zErrMsg);
        if (rc != SQLITE_OK)
        {
            sqlite3_close(db);
            cout<<zErrMsg<<endl;
            sqlite3_free(zErrMsg);
            return "";
        }
        else
        {
            stringstream ss;
            ss << "[";
            cout << "nRow: " << nRow << endl;
            cout << "nCol: " << nCol << endl;
            for(int i=1;i <= nRow;i++)
            {
                ss <<  "\"" << pResult[i*nCol + 1] << "\"";
                if (i != nRow)
                {
                    ss << ", ";
                }
                // cout << pResult[i*nCol + 1] << endl;
            }
            ss << "]";
            sqlite3_free_table(pResult);
            sqlite3_close(db);
            return ss.str();
        }
    }
}


string getTableContent(string params) {
    // params like tableName=name
    string tableName = params.substr(params.find('=') + 1);
    sqlite3 *db;
    char *zErrMsg = nullptr;
    int rc;
    string sql;
    const char *data = "Callback function called";
    
    res = string("");
    
    /* Open database */
    rc = sqlite3_open("db.sqlite3", &db);
    if (rc) {
        printf("Can't open database: %s\n", sqlite3_errmsg(db));
        return string("");
    } else {
        printf("Opened database successfully\n");
    }

    /* Create SQL statement */
    sql = "SELECT * from " + tableName;

    printf("execute sql: %s", sql.c_str());

    /* Execute SQL statement */
    rc = sqlite3_exec(db, sql.c_str(), getTableContentCallback, (void *) data, &zErrMsg);
    res = res.substr(0, res.length() - 1);
    res = "[" + res + "]";
    // printf("%s\n", res.c_str());
    if (rc != SQLITE_OK) {
        printf("SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    } else {
        printf("Operation done successfully\n");
    }
    sqlite3_close(db);
	cout << "<<<<<<<<<<<" << res << endl;

    stringstream ss;
    ss << "{";
    ss << "\"headers\": " << getColName(tableName) << "," << "\"items\": " << res;
    ss << "}"; 
    cout << ss.str() << endl;
    // return res;
    return ss.str();
}

static int insertIntoTableCallback(void *NotUsed, int argc, char **argv, char **azColName) {
    int i;
    for (i = 0; i < argc; i++) {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

string insertIntoTable(string params){
	cout << "[DEBUG] insertIntoTable called!!" << endl;
    cout << "[DEBUG] insertIntoTable params: " << params << endl;
    /*
     * params format
     * tableName=name&col0(该列的真实名称)=value0...
     * 字符串不需要加""，insert时会添加,
     * 例如："{"tableName": "School", "id":123, "class_name": "someClass"}"
     * 会执行INSERT INTO School ("id","class_name") VALUES ("123","someClass");
     */
    sqlite3 *db;
    char *zErrMsg;
    int rc;

    rc = sqlite3_open("db.sqlite3", &db);

    if (rc) {
        printf("[ERROR] 无法加载数据库: %s\n", sqlite3_errmsg(db));
        return "";
    } else {
        printf("[INFO] 成功加载数据库\n");
    }

    // parse json
    Document d;
    d.Parse(params.c_str());
    string tableName = d["TableName"].GetString();
    string colString = "";
    string valueString = "";
    string first = params.substr(0, params.find(','));

    string second = params.substr(params.find(',') + 1);
    while (true) {
        if (second.find(',') != string::npos) {
            first = second.substr(0, second.find(','));
            cout << "first: " << first << endl;
            colString += first.substr(1, first.find(':') - 2);    // key
            colString += ",";
            valueString += first.substr(first.find(':') + 1);       // value
            valueString += ",";
            second = second.substr(second.find(',') + 1);
        }else{
            //colString = colString.substr(0, colString.length() - 1);
            first = second.substr(0, second.find('}'));
            cout << "first: " << first << endl;
            colString += first.substr(1, first.find(':') - 2);    // key
            valueString += first.substr(first.find(':') + 1, first.length());       // value
            break;
        }
    }

    string sql = "INSERT INTO " + tableName + " (" + colString + ") VALUES (" + valueString + ");";
    cout << "execute sql: " << sql << endl;
    /* Execute SQL statement */
    rc = sqlite3_exec(db, sql.c_str(), insertIntoTableCallback, nullptr, &zErrMsg);
    if (rc != SQLITE_OK) {
        printf("[ERROR] SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        char msg[512];
        return "{\"msg\": \"" + string(zErrMsg) + "\"}";
    } else {
        printf("[INFO] 成功在表中插入数据：%s\n", tableName.c_str());
    }

    sqlite3_close(db);

    return "success";
}

static int deleteFromTableCallback(void *NotUsed, int argc, char **argv, char **azColName) {
    int i;
    for (i = 0; i < argc; i++) {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

string deleteFromTable(string params){
    cout << "[DEBUG] delete called!!" << endl;
    cout << "[DEBUG] deleteFromTable params: " << params << endl;
/*
     * params format
     * tableName=name&col0(该列的真实名称)=value0...
     * 字符串不需要加""，insert时会添加,
     * 例如："{"tableName": "School", "id":123, "class_name": "someClass"}"
     * 会执行DELETE FROM School WHERE id=123 and class_name="someClass";
     */

    sqlite3 *db;
    char *zErrMsg;
    int rc;

    rc = sqlite3_open("db.sqlite3", &db);

    if (rc) {
        printf("[ERROR] 无法加载数据库: %s\n", sqlite3_errmsg(db));
        return "";
    } else {
        printf("[INFO] 成功加载数据库\n");
    }

    // parse json
    Document d;
    d.Parse(params.c_str());
    string tableName = d["tableName"].GetString();

    string sql = "DELETE FROM " + tableName + " WHERE ";
    string first = params.substr(0, params.find(','));
    string second = params.substr(params.find(',') + 1);
    while (true) {
        if (second.find(',') != string::npos) {
            first = second.substr(0, second.find(','));
            cout << "first: " << first << endl;
            sql += first.substr(1, first.find(':') - 2);    // key
            sql += "=";
            sql += first.substr(first.find(':') + 1);       // value
            sql += " and ";
            second = second.substr(second.find(',') + 1);
        }else{
            //colString = colString.substr(0, colString.length() - 1);
            first = second.substr(0, second.find('}'));
            cout << "first: " << first << endl;
            sql += first.substr(1, first.find(':') - 2);    // key
            sql += "=";
            sql += first.substr(first.find(':') + 1, first.length());       // value
            sql += ";";
            break;
        }
    }

    cout << "execute sql: " << sql << endl;
    /* Execute SQL statement */
    rc = sqlite3_exec(db, sql.c_str(), deleteFromTableCallback, nullptr, &zErrMsg);
    if (rc != SQLITE_OK) {
        printf("[ERROR] SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        char msg[512];
        return "{\"msg\": \"" + string(zErrMsg) + "\"}";
    } else {
        printf("[INFO] 成功从表中删除数据：%s\n", tableName.c_str());
    }

    sqlite3_close(db);

    return "success";
}

static int updateTableCallback(void *NotUsed, int argc, char **argv, char **azColName) {
    int i;
    for (i = 0; i < argc; i++) {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

string updateTable(string params){
    cout << "[DEBUG] updateTable called!!" << endl;
    cout << "[DEBUG] updateTable params: " << params << endl;
    /*
     * e.g.
     * {"tableName": "course", "oldValue": json, "newValue": json}
     */
    /*
    // parse json
    Document d;
    d.Parse(params.c_str());
    string tableName = d["tableName"].GetString();
    string oldValue = d["oldValue"].GetString();
    string newValue = d["newValue"].GetString();

    deleteFromTable(oldValue);
    insertIntoTable(newValue);
    */

    sqlite3 *db;
    char *zErrMsg;
    int rc;

    rc = sqlite3_open("db.sqlite3", &db);

    if (rc) {
        printf("[ERROR] 无法加载数据库: %s\n", sqlite3_errmsg(db));
        return "";
    } else {
        printf("[INFO] 成功加载数据库\n");
    }

    // parse json
    Document d;
    d.Parse(params.c_str());
    string tableName = d["tableName"].GetString();
    cout << "tableName: " << tableName << endl;
    string oldValue = d["oldValue"].GetString();
    string newValue = d["newValue"].GetString();

    // update tablename set xxx
    string sql = "UPDATE " + tableName + " SET ";
    string first;
    string second = newValue.substr(1, newValue.length());
    while (true) {
        cout << "second: " << second << "\t" << "first: " << first << endl;

        if (second.find(',') != string::npos) {
            first = second.substr(0, second.find(','));

            sql += first.substr(1, first.find(':') - 2);    // key
            sql += "=";
            sql += first.substr(first.find(':') + 1);       // value
            sql += ", ";
            second = second.substr(second.find(',') + 1);
        }else{
            //colString = colString.substr(0, colString.length() - 1);
            first = second.substr(0, second.find('}'));

            sql += first.substr(1, first.find(':') - 2);    // key
            sql += "=";
            sql += first.substr(first.find(':') + 1, first.length());       // value
            break;
        }
    }

    // where xxx
    sql += " WHERE ";
    first = "";
    second = oldValue.substr(1, oldValue.length());
    while (true) {
        cout << "second: " << second << "\t" << "first: " << first << endl;

        if (second.find(',') != string::npos) {
            first = second.substr(0, second.find(','));

            sql += first.substr(1, first.find(':') - 2);    // key
            sql += "=";
            sql += first.substr(first.find(':') + 1);       // value
            sql += " and ";
            second = second.substr(second.find(',') + 1);
        }else{
            //colString = colString.substr(0, colString.length() - 1);
            first = second.substr(0, second.find('}'));
            
            sql += first.substr(1, first.find(':') - 2);    // key
            sql += "=";
            sql += first.substr(first.find(':') + 1, first.length());       // value
            sql += ";";
            break;
        }
    }

    cout << "execute sql: " << sql << endl;
    /* Execute SQL statement */
    rc = sqlite3_exec(db, sql.c_str(), updateTableCallback, nullptr, &zErrMsg);
    if (rc != SQLITE_OK) {
        printf("[ERROR] SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        char msg[512];
        return "{\"msg\": \"" + string(zErrMsg) + "\"}";
    } else {
        printf("[INFO] 成功从表中修改数据：%s\n", tableName.c_str());
    }

    sqlite3_close(db);

    return "success";
}

string getAllTableName(string params) {
    FILE *f = fopen("Config/TableName.txt", "r");
    char buffer[200];
    string res = "[";
    while(fgets(buffer, 200, f)!=nullptr) {
        res += "\"" + string(buffer).substr(0,string(buffer).length()-1) + "\",";

        // cout << "getAllTableName:\t" << res << endl;
    }

    if (res.length() == 1) {
        res = "[]";
    } else {
        res = res.substr(0, res.length()-1);
        res += "]";
    }

    return res;
}


static int createTableCallback(void *NotUsed, int argc, char **argv, char **azColName) {
    int i;
    for (i = 0; i < argc; i++) {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

string createOneTable(string paramStr) {
    sqlite3 *db;
    char *zErrMsg;
    int rc;
    string sql;

    rc = sqlite3_open("db.sqlite3", &db);

    if (rc) {
        printf("[ERROR] 无法加载数据库: %s\n", sqlite3_errmsg(db));
        return "";
    } else {
        printf("[INFO] 成功加载数据库\n");
    }

    // parse json
    Document d;
    d.Parse(paramStr.c_str());
    string tableName = d["tableName"].GetString();
    string tableInfo = d["text"].GetString();
    cout << "tableName: " << tableName << endl;
    cout << "tableInfo: " << tableInfo << endl;

    string createInstr = "CREATE TABLE " + tableName + "(" + tableInfo + ");";
    cout << "execute sql: " << createInstr;
    /* Execute SQL statement */
    rc = sqlite3_exec(db, createInstr.c_str(), createTableCallback, nullptr, &zErrMsg);
    if (rc != SQLITE_OK) {
        printf("[ERROR] SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        
        return "{\"msg\": \"" + replaceDquote(zErrMsg) + "\"}";
    } else {
        printf("[INFO] 成功创建表：%s\n", tableName.c_str());
    }
    FILE *f_table = fopen("Config/TableName.txt", "a");
    if (f_table == nullptr) {
	f_table = fopen("Config/TableName.txt", "w");
    }
    fprintf(f_table, "%s\n", tableName.c_str());
    fclose(f_table);
    sqlite3_close(db);

    return "success";
}


static int dropTableCallback(void *NotUsed, int argc, char **argv, char **azColName) {
    int i;
    for (i = 0; i < argc; i++) {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

string dropOneTable(string paramStr) {
    cout << "dropOneTable:\t" << paramStr << endl;

    sqlite3 *db;
    char *zErrMsg;
    int rc;
    string sql;

    rc = sqlite3_open("db.sqlite3", &db);

    if (rc) {
        printf("[ERROR] 无法加载数据库: %s\n", sqlite3_errmsg(db));
        return "";
    } else {
        printf("[INFO] 成功加载数据库\n");
    }

    // parse json
    Document d;
    d.Parse(paramStr.c_str());
    string tableName = d["tableName"].GetString();
    cout << "tableName: " << tableName << endl;

    string dropInstr = "DROP TABLE " + tableName;
    cout << "execute sql: " << dropInstr;
    /* Execute SQL statement */
    rc = sqlite3_exec(db, dropInstr.c_str(), dropTableCallback, nullptr, &zErrMsg);
    if (rc != SQLITE_OK) {
        printf("[ERROR] SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
        return "{\"msg\": \"" + replaceDquote(zErrMsg) + "\"}";
    } else {
        printf("[INFO] 成功删除表：%s\n", tableName.c_str());
    }

    // 更新TableName.txt
    ifstream in("Config/TableName.txt");

    string fileData = "";
    string temp;
    while(in >> temp) {
        // 逐个词读取
        cout << "table: " << temp << endl;
        if (tableName.compare(temp) != 0) {
            fileData += (temp + "\n");
        }
    }
    in.close();

    ofstream out("Config/TableName.txt");
    out.flush();
    out << fileData;
    out.close();
    sqlite3_close(db);

    return "success";
}

